Relationships and Tables
Note that unlike join operations used in the Data Flow portion of the ETL, the joins that are defined in the Data Model component will not be added to the schema of the source database. Rather, they are only used in the querying process.
In order to build the data model in the most efficient way, Pyramid uses heuristics for a range of purposes in the data modeling process, including defining relationships between the tables in the model. The logic used to auto-define the relationships can be changed, and each join can be edited as required.
Relationships Diagram
The relationship diagram displays all the tables in the data model, and the relationships between the tables. By default, Pyramid uses heuristics to define relationships according to each table's primary key column.
The direction of each join is indicated by the icons at each end of the join:
: indicates that the join comes from this table.
: indicates that the join goes to this table.
Defining Relationships
Pyramid uses heuristics to automatically determine the relationships between tables. The heuristic model can be changed from the ribbon, or by right clicking on the canvas. The Auto-Relationship menu features the following options:
- None: do not use any auto-relationship algorithm, and instead define the relationships manually.
- Use Primary Key as the First Column: by default, this algorithm is used to define relationships between the tables. It takes each table's primary key column and looks for tables that contain the same column as a foreign key.
- Match by Table Name: defines relationships by matching tables my name.
- Match by Column Name: defines relationships by matching columns by name.
Edit Relationships
To edit a relationship, click on its join in the diagram. You will be able to change the join type, or edit the relationship further from the Properties panel.
From the relationship properties, you can change the join type, change the join direction, make the join bidirectional, add, delete, or change joins, change the join operation, and validate the relationship.
Edit Join Keys
The join key table displays the currently selected join. The top row (green highlight below) shows the table where the join column is located; the orange icons displayed beside the table names denotes the direction of the join. The 
indicates that the join is coming from that table, while the 
indicates that the join is going to that table. Change the join's direction by clicking the double arrow icon between the table names.
The second row (purple highlight) displays the join columns; you can change the join column by clicking on it and selecting a different column from the drop-down.
You can also edit a relationship by adding relationships between specified columns directly from the table. To do this, right click on the relevant column, then from the context menu choose the join destination from the Add Relationship sub-menu.
Add Join Keys
To add join keys to the current relationship, click the 'New Join Key' button and select the join columns.
Join Operators
Select the join operator from the drop-down list. By default the join operation is set to = but it can be changed to a non equal operator if required. Equal joins are used to join rows where the primary key columns in each table match. Non equal functions are used to create joins where the key values don't match.
Non equal joins are used for a range of purposes, like checking for duplicate data, generating running totals, or to join according to a range of values or dates. You might want to find the names of salespeople who were hired less than 3 months ago, so their on-boarding and progress can be tracked. Or you might want to find a list of students who are not studying Physics this semester, so you can check if whether or not they have enough credits.
The join operators available are:
- = joins rows where the key in both tables is the same.
- < > joins rows where the key in both tables is not the same.
- > joins rows where the key in the left table is greater than the key in the right table.
- > = joins rows where the key in the left table is greater than or equal to the key in the right table.
- < joins rows where the key in the right table is smaller than the key in the right table.
- < = joins rows where the key in the right table is smaller than or equal to the key in the right table.
Validate
Click the Validate button to test the relationship. If the relationship is valid, you'll receive a green confirmation message. If the relationship is not valid, you'll receive an orange alert with an explanation of the issues found:
